﻿--DailyNotes-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DailyNotes]'))
DROP TABLE [DailyNotes]
GO
CREATE TABLE [DailyNotes](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[Content] [nvarchar](MAX) NOT NULL,
	[IsTodo] [bit] NOT NULL DEFAULT 0,
	[DueDate] [datetime] NULL,
	[DoneDate] [datetime] NULL,
	[IsDeleted] [bit] NOT NULL DEFAULT 0,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_DailyNotes] PRIMARY KEY CLUSTERED ([Key] ASC)) ON [PRIMARY]
GO

----- sp_CreateDailyNotes -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateDailyNotes]'))
DROP PROCEDURE [dbo].[sp_CreateDailyNotes]
GO
CREATE PROCEDURE [sp_CreateDailyNotes](
	@Content  [nvarchar](MAX),
	@IsTodo [bit],
	@DueDate [datetime],
	@Stamp [datetime]
)
AS
BEGIN
	INSERT INTO [dbo].[DailyNotes]([Content], [IsTodo], [DueDate],[CreatedStamp], [LastUpdatedStamp])
	VALUES(@Content, @IsTodo, @DueDate, @Stamp, @Stamp);
END
GO

----- sp_UpdateDailyNotes -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateDailyNotes]'))
DROP PROCEDURE [dbo].[sp_UpdateDailyNotes]
GO
CREATE PROCEDURE [sp_UpdateDailyNotes](
	@Key [uniqueidentifier],
	@Content  [nvarchar](MAX),
	@IsTodo [bit],
	@DueDate [datetime],
	@Stamp [datetime]
)
AS
BEGIN
	UPDATE [dbo].[DailyNotes]
	SET [Content]=@Content , [IsTodo]=@IsTodo, [DueDate]=@DueDate,  [LastUpdatedStamp]=@Stamp
	WHERE [Key]=@Key;
END
GO

----- sp_QueryDailyNotes -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryDailyNotes]'))
DROP PROCEDURE [dbo].[sp_QueryDailyNotes]
GO
CREATE PROCEDURE [sp_QueryDailyNotes](
	@Keyword [nvarchar](MAX),
	@IsTodo bit,
	@IncludeDeleted bit
)
AS
BEGIN
	DECLARE @SqlExp AS nvarchar(Max);
	DECLARE @WhereExp AS nvarchar(Max);

	SET @SqlExp = 'SELECT [Key],[Title],[Url],[Domain],[IsDeleted],[CreatedStamp],[LastUpdatedStamp] FROM [dbo].[LinkBook]';
	SET @WhereExp = '';

	IF(@Keyword IS NOT NULL AND @Keyword <> '')
		SET @WhereExp = @WhereExp +  ' AND (([Type] & ' + CONVERT(NVARCHAR(20), @Type) + ') > 0)';
	IF(@Keyword IS NOT NULL)
	BEGIN
		SET @Keyword = dbo.ReformatLikeStatement(@Keyword);		
		IF(@Keyword <> '')
			SET @WhereExp = @WhereExp +  ' AND ([Name] LIKE ''%' + @Keyword + '%'' OR [Code] LIKE ''%' + @Keyword + '%'')';
	END

	SET @SqlExp = @SqlExp + @WhereExp;
	exec (@SqlExp);
END
GO